Working with CaptureForm Queries

This form allows you to define the properties of a CaptureForm query.

The S Q L builder. The available fields are described below.

Query Name
This is the name of the query as it appears in the Process Tree. The query name isn't used by FormFusion, but lets you create a descriptive name for the query (such as "Get today's date" or "Get length of email address"). The query name can be up to 259 characters in length.

Data Connection Override
The Environment properties specify a default data connection, which you can override at the query level by changing the value in this drop down. If the selection is left as <None>, then it uses the default data connection.

When will this query execute?
This drop down controls when the query will execute. Most queries will be "Page" queries.

Pre - These queries execute before any data has been processed on the server side.
Page - These queries execute before each page is processed.
Post - These queries execute after the entire job is complete.
Test - This prevents the query from executing on the server.

This query populates variable(s) used to sort the output.
To facilitate the sorting process you must specify which queries should be executed during the sort. This decreases program latency by only executing the queries that you instruct FormFusion to execute. Sorting is performed before the processing of the report file is begun.

Terminate processing of the template if this query returns an error
Checking this checkbox will force FormFusion to terminate the current process being executed if a error occurs in this query. If you want processing to continue regardless if there is an error in this query, then leave this unchecked.

SQL Script
This box is where you enter the SQL query that defines the actions for this CaptureForm query. Any standard SQL statement can be entered here. This includes the statements SELECT, INSERT INTO, UPDATE, and DELETE. Variables can be used almost anywhere in the query. The most common use for variables are in the INTO clause of a SELECT statement or in the WHERE clause. Following are some examples of queries and their use:

select EMPFIRST || EMPMIDDLE, EMPLAST
into :EmpFirst_and_M, :EmpLast from EMP
where EMPID = :employee_ID

This query retrieves the First Name, Middle Name, and Last Name fields from the EMP table and place the results into variables "EmpFirst_and_M", and "EmpLast". The constraint placed on the search is that EMPID should equal the value contained in "employee_ID" which is defined elsewhere in FormFusion (currently either in CaptureForm or MapForm).

update EMP

set EMPFULLNAME = :EmpLast || ', ' || :EmpFirst_and_M

where EMPID = :employee_ID

This query updates the records for the employee with ID equal "employee_ID" in the EMP table. It combines the two string variables created in the above SELECT query into one string and places that value into EMPFULLNAME.

Note: Do not enter a semicolon at the end of your SQL statement. If you are copying a query from another application that uses semicolon notation, you should make sure to remove the semicolon when pasting the query into FormFusion.

Note:

The SQL syntax depends on the database you are using (Oracle, SQL Server, etc.).
You can use any SQL statements or functions that work for your database.
You can reference or update MapForm and CaptureForm variables in the query.
Functions such as Oracle's REGEXP, REPLACE, and many others can be used to modify data stored in MapForm and CaptureForm variables. Other databases may have similar functions available. This technique can be used to change data from the MapForm into a different format, to strip out extra spaces, remove unneeded data, and much more.
If you are trying to INSERT, UPDATE, DELETE, or run a database script, your MAPS administrator will need to ensure that these types of statements are permitted in the User/Group Rules settings for the data connection.

Tab Toggle - Selecting the button changes the behavior of the Tab key so that it will insert a Tab character into the SQL Script box instead of tabbing out of the box.

Select a maximum of # record(s).
This field allows you to specify the maximum number of records to retrieve for the variables in the INTO clause of a SELECT statement. This field is only used for SELECT statements. The default is to retrieve only one record (the first row). To retrieve all records, set this field to 0.

Test Query
The Test Query button (green triangle) allows you to test the query you created without committing the results. You will be prompted for the values of all variables in the query. After testing the query you can save the results as sample data for preview within FormStamp. Most common SQL statements will be rolled back after executing a test (e.g. SELECT, INSERT, or DELETE). REST or web-based connections may not support the ability to roll back certain SQL statements.

Save Changes
You can save your changes by clicking on the Save icon on the toolbar. Any changes that are made are not saved to the database until you click this button. Similarly, if you have made a mistake you can click the "Revert Changes" button which will revert all changes since the last time you committed.